package controllers;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import model.CaseGoods;
import model.T_RelationGridGoods;
import model.User_list;

import com.et.ar.exception.ActiveRecordException;
import com.et.mvc.JsonView;

/**
 * 
 * CasePropertyPrintController.java
 * @author crab
 *
 * Remark:打印用的controller
 */
public class CasePropertyPrintController extends ApplicationController {
	private static final String enter = " \n";
	public JsonView getStoredGoods(int rows, int page) throws ActiveRecordException{
		User_list ul = (User_list)session.getAttribute("sessionAccount");
		String company_id = ul.getUser_company();
		StringBuilder condition = new StringBuilder();
		condition.append("1=1 "); 
		condition.append(" and operate_company=").append(company_id).append(enter); 
		String ajmc = request.getParameter("AJMC");//案件名称
		if(ajmc!=null && ajmc.length()>0)
			buildCondition(condition,"%"+ajmc+"%","TCGI.CASE_NAME","like");
		String wpmc = request.getParameter("WPMC");//物品名称
		if(wpmc!=null && wpmc.length()>0)
			buildCondition(condition,"%"+wpmc+"%","TCGI.GOODS_NAME","like");
		String badw = request.getParameter("BADW");//办案单位
		if(badw!=null && badw.length()>0)
			buildCondition(condition,"%"+badw+"%","TCGI.CASE_COMPANY","like");
		String bar = request.getParameter("BAR");//办案人员
		if(bar!=null && bar.length()>0)
			buildCondition(condition,"%"+bar+"%","TCGI.CASE_MAN","like");
		
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT TRGG.STATUS,").append(enter);
		sql.append("       TCGI.GOODS_NAME,").append(enter);
		sql.append("       TCGI.GOODS_ID,").append(enter);
		sql.append("       TCGI.GOODS_KIND,").append(enter);
		sql.append("       TCGI.CASE_NAME,").append(enter);
		sql.append("       TCGI.CASE_NUM,").append(enter);
		sql.append("       TRGG.CREATE_DATE,").append(enter);
		sql.append("       TCRI.ROOM_NAME,").append(enter);
		sql.append("       TCSI.SHELF_NAME,").append(enter);
		sql.append("       TCGRI.GRID_NUM,").append(enter);
		sql.append("       TRGG.T_CODE").append(enter); 
		sql.append("  FROM T_RELATION_GRID_GOODS TRGG").append(enter);
		sql.append(" INNER JOIN T_CASE_GOODS_INFO TCGI ON TRGG.T_CODE = TCGI.GOODS_TCODE  AND NVL(TCGI.STATUS, 0) = 0").append(enter);
		sql.append(" INNER JOIN T_CASE_ROOM_INFO TCRI ON TRGG.CASE_ROOM_ID = TCRI.ROOM_ID").append(enter);
		sql.append(" INNER JOIN T_CASE_SHELF_INFO TCSI ON TRGG.CASE_SHELF_ID = TCSI.SHELF_ID").append(enter);
		sql.append(" INNER JOIN T_CASE_GRID_INFO TCGRI ON TRGG.CASE_GRID_ID = TCGRI.GRID_ID").append(enter);
		sql.append(" where ").append(condition.toString());
		sql.append("ORDER BY case_num");
		System.out.println(sql.toString());
		StringBuilder count_sql = new StringBuilder();
		count_sql.append("select count(*) count").append(enter);
		count_sql.append("  FROM T_RELATION_GRID_GOODS TRGG").append(enter);
		count_sql.append(" INNER JOIN T_CASE_GOODS_INFO TCGI ON TRGG.T_CODE = TCGI.GOODS_TCODE  AND NVL(TCGI.STATUS, 0) = 0").append(enter);
		count_sql.append(" INNER JOIN T_CASE_ROOM_INFO TCRI ON TRGG.CASE_ROOM_ID = TCRI.ROOM_ID").append(enter);
		count_sql.append(" INNER JOIN T_CASE_SHELF_INFO TCSI ON TRGG.CASE_SHELF_ID = TCSI.SHELF_ID").append(enter);
		count_sql.append(" INNER JOIN T_CASE_GRID_INFO TCGRI ON TRGG.CASE_GRID_ID = TCGRI.GRID_ID").append(enter);
		count_sql.append(" where ").append(condition.toString());
		List<Map<String,Object>> count_map = T_RelationGridGoods.getResultMap(T_RelationGridGoods.class,count_sql.toString(),null, "",0,Integer.MAX_VALUE);
		Long total = count_map.size()>0?Long.parseLong(count_map.get(0).get("count").toString()):1;
		List<Map<String,Object>> shelfGoodslist = T_RelationGridGoods.getResultMap(T_RelationGridGoods.class,sql.toString(),null, "",0,Integer.MAX_VALUE);
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("total", total);//数据总数
		result.put("rows", shelfGoodslist);
		return new JsonView(result);
	}
	/**
	 * 构造查询条件得方法
	 * @param cond 查询条件，第一次是 1=1 ，然后不停得拼接 and条件
	 * @param value 查询得值，如果是like查询，需要手动加'%' %value%
	 * @param target 目标字段
	 * @param operate 操作符
	 */
	private void buildCondition(StringBuilder cond,String value,String target,String operate){
		if(value!=null && value.length()>0){
			cond.append(" and ").append(target).append(" ").append(operate).append(" '").append(value).append("'");
		}
	}
	public JsonView getGoodsInfo(int goods_id,String goods_kind) throws ActiveRecordException{
		StringBuffer sql = new StringBuffer();
		if("01".equals(goods_kind)){
			//一般物品查询 T_GOODS_ORDINATY
			buildOrdinaty(sql,goods_id,goods_kind);
		}else if("02".equals(goods_kind)){
			//票据 查询T_GOODS_CASH
			buildCash(sql,goods_id,goods_kind);
		}else if("05".equals(goods_kind)){
			//金银 查询T_GOODS_ANTIQUE
			buildAntique(sql,goods_id,goods_kind);
		}else if("06".equals(goods_kind)){
			//录像 查询T_GOODS_VIDEO
			buildVideo(sql,goods_id,goods_kind);
		}else if("07".equals(goods_kind)){
			//不动产 查询T_GOODS_IMMOVABLE
			buildImmovable(sql,goods_id,goods_kind);
		}else if("13".equals(goods_kind)){
			//汽车 查询T_GOODS_CAR
			buildCar(sql,goods_id,goods_kind);
		}
		List<Map<String,Object>> list = CaseGoods.getResultMap(CaseGoods.class, sql.toString(), null, "",0,Integer.MAX_VALUE);
		return new JsonView(list.get(0));
	} 
	private void buildOrdinaty(StringBuffer sql,int goods_id,String goods_kind){
		sql.append("SELECT tcgi.goods_id,").append(enter);
		sql.append("       tcgi.goods_name,").append(enter);
		sql.append("       tdc.c_name goods_type,").append(enter);
		sql.append("       tdc1.c_name case_type,").append(enter);
		sql.append("       nvl(tcgi.goods_values,0) goods_values,").append(enter);
		sql.append("       tcgi.case_name,").append(enter);
		sql.append("       tdc2.c_name goods_kind,").append(enter);
		sql.append("       tdc3.c_name case_status,").append(enter);
		sql.append("       tcgi.goods_desc,").append(enter);
		sql.append("       tcgi.case_simple_desc,").append(enter);
		sql.append("       tgo.ordinary_brand,").append(enter);
		sql.append("       tgo.ordinary_model,").append(enter);
		sql.append("       tgo.ordinary_num,").append(enter);
		sql.append("       tgo.ordinary_weight,").append(enter);
		sql.append("       tgo.ordinary_fac,").append(enter);
		sql.append("       tgo.ordinary_grain,").append(enter);
		sql.append("       tdc4.c_name ordinary_color").append(enter);
		sql.append("  FROM T_CASE_GOODS_INFO TCGI").append(enter);
		sql.append(" INNER JOIN T_GOODS_ORDINATY TGO ON TCGI.GOODS_ID = ").append(goods_id).append(enter);
		sql.append("                                AND TCGI.GOODS_ID = TGO.GOODS_ID").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc ON tcgi.goods_type=tdc.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc1 ON tcgi.case_type = tdc1.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc2 ON tdc2.c_code ='").append(goods_kind).append("' AND tdc2.p_code=4").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc3 ON tdc3.c_code = tcgi.case_status AND tdc3.p_code=14").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc4 ON tdc4.c_code = tgo.ordinary_color AND tdc4.p_code=16").append(enter);
	}
	private void buildCash(StringBuffer sql,int goods_id,String goods_kind){
		sql.append("SELECT tcgi.goods_id,").append(enter);
		sql.append("       tcgi.goods_name,").append(enter);
		sql.append("       tdc.c_name goods_type,").append(enter);
		sql.append("       tdc1.c_name case_type,").append(enter);
		sql.append("       nvl(tcgi.goods_values,0) goods_values,").append(enter);
		sql.append("       tcgi.case_name,").append(enter);
		sql.append("       tdc2.c_name goods_kind,").append(enter);
		sql.append("       tdc3.c_name case_status,").append(enter);
		sql.append("       tcgi.goods_desc,").append(enter);
		sql.append("       tcgi.case_simple_desc,").append(enter);
		sql.append("       tgc.cash_cost,").append(enter);
		sql.append("       tgc.cash_value,").append(enter);
		sql.append("       tgc.cash_num,").append(enter);
		sql.append("       tdc4.c_name cash_type").append(enter);
		sql.append("  FROM T_CASE_GOODS_INFO TCGI").append(enter);
		sql.append(" INNER JOIN T_GOODS_CASH tgc ON TCGI.GOODS_ID = tgc.GOODS_ID").append(enter);
		sql.append("                               AND TCGI.GOODS_ID = ").append(goods_id).append(enter);
		sql.append("LEFT JOIN t_dict_child tdc ON tcgi.goods_type=tdc.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc1 ON tcgi.case_type = tdc1.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc2 ON tdc2.c_code ='").append(goods_kind).append("' AND tdc2.p_code=4").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc3 ON tdc3.c_code = tcgi.case_status AND tdc3.p_code=14").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc4 ON tdc4.c_code = tgc.cash_type AND tdc4.p_code=17").append(enter);
	}
	private void buildAntique(StringBuffer sql,int goods_id,String goods_kind){
		sql.append("SELECT tcgi.goods_id,").append(enter);
		sql.append("       tcgi.goods_name,").append(enter);
		sql.append("       tdc.c_name goods_type,").append(enter);
		sql.append("       tdc1.c_name case_type,").append(enter);
		sql.append("       nvl(tcgi.goods_values,0) goods_values,").append(enter);
		sql.append("       tcgi.case_name,").append(enter);
		sql.append("       tdc2.c_name goods_kind,").append(enter);
		sql.append("       tdc3.c_name case_status,").append(enter);
		sql.append("       tcgi.goods_desc,").append(enter);
		sql.append("       tcgi.case_simple_desc,").append(enter);
		sql.append("       tga.antique_num,").append(enter);
		sql.append("       tdc4.c_name antique_level,").append(enter);
		sql.append("       tdc5.c_name antique_color,").append(enter);
		sql.append("       tga.antique_grain").append(enter);
		sql.append("  FROM T_CASE_GOODS_INFO TCGI").append(enter);
		sql.append(" INNER JOIN T_GOODS_ANTIQUE tga ON TCGI.GOODS_ID = tga.GOODS_ID").append(enter);
		sql.append("                               AND TCGI.GOODS_ID = ").append(goods_id).append(enter);
		sql.append("LEFT JOIN t_dict_child tdc ON tcgi.goods_type=tdc.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc1 ON tcgi.case_type = tdc1.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc2 ON tdc2.c_code ='").append(goods_kind).append("' AND tdc2.p_code=4").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc3 ON tdc3.c_code = tcgi.case_status AND tdc3.p_code=14").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc4 ON tdc4.c_code = tga.antique_level AND tdc4.p_code=19").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc5 ON tdc5.c_code = tga.antique_color AND tdc5.p_code=16").append(enter);
	}
	private void buildVideo(StringBuffer sql,int goods_id,String goods_kind){
		sql.append("SELECT tcgi.goods_id,").append(enter);
		sql.append("       tcgi.goods_name,").append(enter);
		sql.append("       tdc.c_name goods_type,").append(enter);
		sql.append("       tdc1.c_name case_type,").append(enter);
		sql.append("       nvl(tcgi.goods_values,0) goods_values,").append(enter);
		sql.append("       tcgi.case_name,").append(enter);
		sql.append("       tdc2.c_name goods_kind,").append(enter);
		sql.append("       tdc3.c_name case_status,").append(enter);
		sql.append("       tcgi.goods_desc,").append(enter);
		sql.append("       tcgi.case_simple_desc,").append(enter);
		sql.append("       tgv.video_type,").append(enter);
		sql.append("       tgv.video_obj,").append(enter);
		sql.append("       tgv.video_content,").append(enter);
		sql.append("       tgv.video_brief,").append(enter);
		sql.append("       tgv.video_sub,").append(enter);
		sql.append("       tgv.video_mode,").append(enter);
		sql.append("       tgv.video_length,").append(enter);
		sql.append("       tgv.video_date").append(enter);
		sql.append("  FROM T_CASE_GOODS_INFO TCGI").append(enter);
		sql.append(" INNER JOIN T_GOODS_VIDEO tgv ON TCGI.GOODS_ID = tgv.GOODS_ID").append(enter);
		sql.append("                               AND TCGI.GOODS_ID = ").append(goods_id).append(enter);
		sql.append("LEFT JOIN t_dict_child tdc ON tcgi.goods_type=tdc.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc1 ON tcgi.case_type = tdc1.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc2 ON tdc2.c_code ='").append(goods_kind).append("' AND tdc2.p_code=4").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc3 ON tdc3.c_code = tcgi.case_status AND tdc3.p_code=14").append(enter);
	}
	private void buildImmovable(StringBuffer sql,int goods_id,String goods_kind){
		sql.append("SELECT tcgi.goods_id,").append(enter);
		sql.append("       tcgi.goods_name,").append(enter);
		sql.append("       tdc.c_name goods_type,").append(enter);
		sql.append("       tdc1.c_name case_type,").append(enter);
		sql.append("       nvl(tcgi.goods_values,0) goods_values,").append(enter);
		sql.append("       tcgi.case_name,").append(enter);
		sql.append("       tdc2.c_name goods_kind,").append(enter);
		sql.append("       tdc3.c_name case_status,").append(enter);
		sql.append("       tcgi.goods_desc,").append(enter);
		sql.append("       tcgi.case_simple_desc,").append(enter);
		sql.append("       tgi.immovable_validity,").append(enter);
		sql.append("       tgi.immovable_status,").append(enter);
		sql.append("       tgi.immovable_quate,").append(enter);
		sql.append("       tgi.immovable_relatoin,").append(enter);
		sql.append("       tgi.immovable_ceri").append(enter);
		sql.append("  FROM T_CASE_GOODS_INFO TCGI").append(enter);
		sql.append(" INNER JOIN T_GOODS_VIDEO tgi ON TCGI.GOODS_ID = tgi.GOODS_ID").append(enter);
		sql.append("                               AND TCGI.GOODS_ID = ").append(goods_id).append(enter);
		sql.append("LEFT JOIN t_dict_child tdc ON tcgi.goods_type=tdc.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc1 ON tcgi.case_type = tdc1.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc2 ON tdc2.c_code ='").append(goods_kind).append("' AND tdc2.p_code=4").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc3 ON tdc3.c_code = tcgi.case_status AND tdc3.p_code=14").append(enter);
	} 
	private void buildCar(StringBuffer sql,int goods_id,String goods_kind){
		sql.append("SELECT tcgi.goods_id,").append(enter);
		sql.append("       tcgi.goods_name,").append(enter);
		sql.append("       tdc.c_name goods_type,").append(enter);
		sql.append("       tdc1.c_name case_type,").append(enter);
		sql.append("       nvl(tcgi.goods_values,0) goods_values,").append(enter);
		sql.append("       tcgi.case_name,").append(enter);
		sql.append("       tdc2.c_name goods_kind,").append(enter);
		sql.append("       tdc3.c_name case_status,").append(enter);
		sql.append("       tcgi.goods_desc,").append(enter);
		sql.append("       tcgi.case_simple_desc,").append(enter);
		sql.append("       tgc.car_owner,").append(enter);
		sql.append("       tgc.car_no,").append(enter);
		sql.append("       tdc4.c_name car_type,").append(enter);
		sql.append("       tgc.car_spot,").append(enter);
		sql.append("       tgc.car_mileage,").append(enter);
		sql.append("       tdc5.c_name car_color,").append(enter);
		sql.append("       tgc.car_status,").append(enter);
		sql.append("       tgc.car_idcard").append(enter);
		sql.append("  FROM T_CASE_GOODS_INFO TCGI").append(enter);
		sql.append(" INNER JOIN T_GOODS_CAR tgc ON TCGI.GOODS_ID = tgc.GOODS_ID").append(enter);
		sql.append("                               AND TCGI.GOODS_ID = ").append(goods_id).append(enter);
		sql.append("LEFT JOIN t_dict_child tdc ON tcgi.goods_type=tdc.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc1 ON tcgi.case_type = tdc1.c_code").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc2 ON tdc2.c_code ='").append(goods_kind).append("' AND tdc2.p_code=4").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc3 ON tdc3.c_code = tcgi.case_status AND tdc3.p_code=14").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc4 ON tdc4.c_code = tgc.car_type AND tdc4.p_code=18").append(enter);
		sql.append("LEFT JOIN t_dict_child tdc5 ON tdc5.c_code = tgc.car_color AND tdc5.p_code=16").append(enter);
	}
}
